﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;

namespace CodeBuilder.Code
{
    public class SqlStructure
    {
        private List<string> tableList;
        private List<string> viewList;

        private ILookup<string, ColumnInfo> tableLookup;
        private ILookup<string, ColumnInfo> viewLookup;
        

        #region Helpers

        public static string LastConName(string Constraint_Name)
        {
            return Constraint_Name.Substring(Constraint_Name.LastIndexOf('_') + 1, Constraint_Name.Length - Constraint_Name.LastIndexOf('_') - 1);
        }

        public static string FirstConName(string Constraint_Name)
        {
            return Constraint_Name.Substring(Constraint_Name.IndexOf('_') + 1, Constraint_Name.LastIndexOf('_') - Constraint_Name.IndexOf('_') - 1);
        }

        #endregion

        public SqlStructure(string connString)
        {
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                tableLookup = GetTableLookup(conn);
                viewLookup = GetViewLookup(conn);

                this.tableList = tableLookup.Select(l => l.Key).ToList();
                this.viewList = viewLookup.Select(l => l.Key).ToList();
            }
            
        }

        public List<string> Tables
        {
            get
            {
                return tableList;
            }
        }

        public List<string> Views { get { return viewList; } }

   

        public IEnumerable<ColumnInfo> GetColumns(string table)
        {
            if (this.tableLookup.Contains(table))
                return this.tableLookup[table];
            return this.viewLookup[table];
        }


        private ILookup<string, ColumnInfo> GetTableLookup(IDbConnection conn)
        {
            var cmd = conn.CreateCommand();

            cmd.CommandText = @"
                    select 
                    objs.Name AS entity,
                    cols.Name AS name,	
                    tpes.name AS type,	
                    cols.max_length,
                    cols.is_nullable AS allownull,
                    (case when idns.object_id is null then 0 else 1 end) as [identity],
                    isnull(pros.value,'') as [desc],
                    isnull((SELECT 1 FROM sysobjects where xtype='PK' and  parent_obj=cols.object_id and name in (  
                      SELECT name  FROM sysindexes   WHERE indid in(  
                      SELECT indid FROM sysindexkeys WHERE id = cols.object_id AND colid=cols.column_id  
                    ))),0) as iskey
                    from
                    sys.objects objs
                    JOIN sys.columns cols
                    ON objs.object_id = cols.object_id
                    JOIN sys.types tpes on cols.system_type_id = tpes.system_type_id AND tpes.name != 'sysname'
                    LEFT JOIN sys.identity_columns idns ON idns.object_id = objs.object_id AND idns.column_id = cols.column_id
                    LEFT JOIN sys.extended_properties pros on pros.major_id = cols.object_id AND pros.minor_id = cols.column_id
                    Where objs.type = 'U'
                    ORDER BY objs.Name";

            var columnList = new List<ColumnInfo>();
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    columnList.Add(new ColumnInfo
                    {
                        entity = reader.GetString(0),
                        name = reader.GetString(1),
                        type = reader.GetString(2),
                        length = reader.GetInt16(3),
                        allownull = reader.GetBoolean(4),
                        identity = reader.GetInt32(5) > 0,
                        desc = reader.GetString(6),
                        iskey = reader.GetInt32(7) > 0
                    });
                }
            }
            return columnList.OrderBy(c => c.entity).ToLookup(c => c.entity);
        }

        private ILookup<string, ColumnInfo> GetViewLookup(IDbConnection conn)
        {
            var cmd = conn.CreateCommand();


            cmd.CommandText = @"
                select 
                objs.Name AS entity,
                cols.Name AS name,	
                tpes.name AS type,	
                cols.max_length,
                cols.is_nullable AS allownull,
                (case when idns.object_id is null then 0 else 1 end) as [identity],
                isnull(pros.value,'') as [desc],
                isnull((SELECT 1 FROM sysobjects where xtype='PK' and  parent_obj=cols.object_id and name in (  
                  SELECT name  FROM sysindexes   WHERE indid in(  
                  SELECT indid FROM sysindexkeys WHERE id = cols.object_id AND colid=cols.column_id  
                ))),0) as iskey
                from
                sys.objects objs
                JOIN sys.columns cols
                ON objs.object_id = cols.object_id
                JOIN sys.types tpes on cols.system_type_id = tpes.system_type_id AND tpes.name != 'sysname'
                LEFT JOIN sys.identity_columns idns ON idns.object_id = objs.object_id AND idns.column_id = cols.column_id
                LEFT JOIN sys.extended_properties pros on pros.major_id = cols.object_id AND pros.minor_id = cols.column_id
                Where objs.type = 'V'
                ORDER BY objs.Name";

            var columnList = new List<ColumnInfo>();
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    columnList.Add(new ColumnInfo
                    {
                        entity = reader.GetString(0),
                        name = reader.GetString(1),
                        type = reader.GetString(2),
                        length = reader.GetInt16(3),
                        allownull = reader.GetBoolean(4),
                        identity = reader.GetInt32(5) > 0,
                        desc = reader.GetString(6),
                        iskey = reader.GetInt32(7) > 0,
                        inview = true
                    });
                }
            }
            return columnList.OrderBy(c => c.entity).ToLookup(c => c.entity);

        }
        
        /// <summary>
        /// 数据库类型到CLR类型转换
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string DbToCLR(string type)
        {
            switch (type)
            {

                case "date":
                case "datetime":
                case "smalldatetime":
                case "time":
                    return "DateTime";

                case "uniqueide":
                case "uniqueidentifier":
                    return "Guid";

                case "nchar":
                case "varchar":
                case "nvarchar":
                case "string":
                case "ntext":
                case "text":
                case "char":
                case "longtext":
                case "sysname":
                case "xml":
                    return "string";

                case "integer":
                    return "int";

                case "money":
                case "real":
                case "numeric":
                case "smallmoney":
                case "float":
                case "decimal":
                case "number":
                    return "decimal";

                case "int":
                case "smallint":
                case "tinyint":
                    return "int";

                case "bit":
                case "boolean":
                    return "bool";

                case "bfile":
                case "binary":
                case "image":
                case "varbinary":
                case "long":
                case "longblob":    // mysql
                case "blob":            // mysql
                case "timestamp":
                    return "byte[]";


                case "bigint":
                    return "long";

                default:
                    return "unknow(" + type + ")";
            }
        }

    }
}
